package  com.rh.soc.equipmenttypethirdinterface.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Service;

import com.rh.soc.equipmenttypethirdinterface.dao.IEquipmentTypeThirdDAO;
import com.rh.soc.management.basedata.equipmenttype.model.DeviceTypeConstant;
import com.rh.soc.management.basedata.equipmenttype.model.EquipmentClassVO;
import com.rh.soc.management.basedata.equipmenttype.model.EquipmentTypeVO;
import com.rh.webserver.common.base.dao.BaseDao;
import com.rh.webserver.common.util.SqlUtil;
import com.rh.webserver.common.util.StringUtil;

@Service
public class EquipmentTypeDaoThirdImpl extends BaseDao implements IEquipmentTypeThirdDAO {

	/**
	 * 页面初始，查询所有数据；修改时根据id查询单一数据
	 * 
	 */
	public List<EquipmentTypeVO> queryEquipmentTypeData(EquipmentTypeVO vo) {
		String device_type = String.valueOf(vo.getDeviceType());
		String old_device_type = String.valueOf(vo.getOldDeviceType());
		String typename = vo.getDeviceTypeName();
		String deviceTypeClass = vo.getDeviceTypeClass();
		StringBuilder sql = new StringBuilder(128);
		sql.append(" with osType as (select wm_concat(a.os_code) osType, wm_concat(b.pt_name) osName, a.device_type "
				+ " from T_DEVTYPE_VS_OS a "
				+ " left join t_public_types b on a.os_code = b.pt_id and b.pt_type = 'OS_TYPE' group by a.device_type)");
		sql.append(" SELECT c.device_type,c.device_type_name,c.device_type_map,c.device_type_sort,c.device_class,t."
				+ "device_class_name, t1.osType, t1.osName ");
		sql.append(" FROM t_device_type c left join osType t1 on c.device_type = t1.device_type "
				+ " left join t_device_class t on t.device_class=c.device_class WHERE" + " c.device_type_class= ?");
		Object[] o = null;
		List<Object> obj = new ArrayList<Object>();
		obj.add(deviceTypeClass);
		if (StringUtils.isNotEmpty(old_device_type) && !"0".equals(old_device_type)) {
			sql.append(" AND c.device_type = ?");
			obj.add(vo.getOldDeviceType());
		}
		if (StringUtils.isNotEmpty(device_type) && null != device_type && !"null".equals(device_type)
				&& !"0".equals(device_type)) {
			sql.append(" AND c.device_type = ?");
			obj.add(device_type);
		}
		if (StringUtils.isNotEmpty(typename) && !"null".equals(typename)) {
			sql.append(" AND LOWER(c.device_type_name) LIKE LOWER('%" + SqlUtil.convertSqlParam(typename) + "%') escape '\\'  ");

		}
		if (vo.getDeviceClass() == 0) {
			if (StringUtil.isNotEmpty(vo.getDeviceClassName())) {
				try {
					vo.setDeviceClass(Long.parseLong(vo.getDeviceClassName()));
				} catch (Exception e) {
				}
			}
		}
		vo.getDeviceClass();
		if (vo.getDeviceClass() != 0) {
			sql.append(" AND c.device_class = ?");
			obj.add(vo.getDeviceClass());
		}
		/** 未知设备不能操作，不用展示到页面 */
		sql.append(" AND c.device_type != 501");
		sql.append(" ORDER BY device_type_sort DESC");
		String str = this.packageSqlForOrderBy(sql.toString(), vo.getOrderBy(), vo.getOrder());
		o = obj.toArray();
		return this.query(str, vo, o);
	}
	
	
	public List<EquipmentTypeVO> queryEquipmentTypeDataUpdate(EquipmentTypeVO vo) {
		String device_type = String.valueOf(vo.getDeviceType());
		String old_device_type = String.valueOf(vo.getOldDeviceType());
		String typename = vo.getDeviceTypeName();
		String deviceTypeClass = vo.getDeviceTypeClass();
		StringBuilder sql = new StringBuilder(128);
		sql.append(" with osType as (select wm_concat(a.os_code) osType, wm_concat(b.pt_name) osName, a.device_type "
				+ " from T_DEVTYPE_VS_OS a "
				+ " left join t_public_types b on a.os_code = b.pt_id and b.pt_type = 'OS_TYPE' group by a.device_type)");
		sql.append(" SELECT c.device_type,c.device_type_name,c.device_type_map,c.device_type_sort,c.device_class,t."
				+ "device_class_name, t1.osType, t1.osName ");
		sql.append(" FROM t_device_type c left join osType t1 on c.device_type = t1.device_type "
				+ " left join t_device_class t on t.device_class=c.device_class WHERE" + " c.device_type_class= ?");
		Object[] o = null;
		List<Object> obj = new ArrayList<Object>();
		obj.add(deviceTypeClass);
		if (StringUtils.isNotEmpty(old_device_type) && !"0".equals(old_device_type)) {
			sql.append(" AND c.device_type = ?");
			obj.add(vo.getOldDeviceType());
		}
		if (StringUtils.isNotEmpty(device_type) && null != device_type && !"null".equals(device_type)
				&& !"0".equals(device_type)) {
			sql.append(" AND c.device_type != ?");
			obj.add(device_type);
		}
		if (StringUtils.isNotEmpty(typename) && !"null".equals(typename)) {
			sql.append(" AND LOWER(c.device_type_name) LIKE LOWER('%" + SqlUtil.convertSqlParam(typename) + "%') escape '\\'  ");

		}
		if (vo.getDeviceClass() == 0) {
			if (StringUtil.isNotEmpty(vo.getDeviceClassName())) {
				try {
					vo.setDeviceClass(Long.parseLong(vo.getDeviceClassName()));
				} catch (Exception e) {
				}
			}
		}
		vo.getDeviceClass();
		if (vo.getDeviceClass() != 0) {
			sql.append(" AND c.device_class = ?");
			obj.add(vo.getDeviceClass());
		}
		/** 未知设备不能操作，不用展示到页面 */
		sql.append(" AND c.device_type != 501");
		sql.append(" ORDER BY device_type_sort DESC");
		String str = this.packageSqlForOrderBy(sql.toString(), vo.getOrderBy(), vo.getOrder());
		o = obj.toArray();
		return this.query(str, vo, o);
	}
	
	

	/**
	 * 查询总类型数据
	 * 
	 */
	public List<EquipmentClassVO> queryClassType(EquipmentClassVO vo) {
		String sql = "SELECT device_class,device_class_name,device_class_sort FROM t_device_class ORDER BY device_class_sort";
		return this.query(sql, vo);
	}

	/**
	 * 
	 * {查询操作系统列表}
	 * 
	 * @param vo
	 * @return
	 * @author:邵珅
	 */
	public List<EquipmentClassVO> queryOsType(EquipmentClassVO vo) {
		String sql = "select p.pt_id device_class,p.pt_name device_class_name from t_public_types p where p.pt_type = 'OS_TYPE' ORDER BY pt_name";
		return this.query(sql, vo);

	}

	/**
	 * 添加页面保存数据
	 * 
	 */
	public int addEquipmentTypeSave(EquipmentTypeVO vo) {
		long sortLong=0;
		String sql2;
		try {
			sql2 = "SELECT max(device_type_sort) FROM t_device_type ORDER BY device_type_sort DESC";
			sortLong = this.queryForLong(sql2);
		} catch (Exception e) {
			sortLong=0;
		}
		
		long max =sortLong + 1;
		String sql;
		Object[] o;
		String deviceType = nextVal("seq_device_type");
		if ("".equals(vo.getDeviceType()) || null == vo.getDeviceType()) {
			vo.setDeviceType(deviceType);
			sql = "INSERT INTO t_device_type  ( device_type, device_class ,device_type_name,device_type_map ,"
					+ "device_type_sort,device_type_class) VALUES (?,?,?,?,?,0)";
			o = new Object[] { vo.getDeviceType(), vo.getDeviceClass(), vo.getDeviceTypeName(), vo.getDeviceTypeMap(),
					max };
		} else {
			sql = "INSERT INTO t_device_type  ( device_type, device_class ,device_type_name,device_type_map ,"
					+ "device_type_sort,device_type_class) VALUES (?,?,?,?,?,0)";
			o = new Object[] { vo.getDeviceType(), vo.getDeviceClass(), vo.getDeviceTypeName(), vo.getDeviceTypeMap(),
					max };
		}
		return this.update(sql, o);
	}

	/**
	 * 保存修改数据
	 * 
	 */
	public int updEquipmentTypeSave(EquipmentTypeVO vo) {
		String sql = "UPDATE t_device_type SET device_type_name = ? ,device_class = ? , device_type_map = ?"
				+ " WHERE device_type = ?";
		Object[] o = new Object[] { vo.getDeviceTypeName(), vo.getDeviceClass(), vo.getDeviceTypeMap(),
				vo.getDeviceType() };
		return this.update(sql, o);
	}

	/**
	 * 删除选择数据
	 * 
	 */
	public int delEquipmentType(EquipmentTypeVO vo) {
		String sql = "DELETE FROM t_device_type WHERE device_type = ?";
		Object[] o = new Object[] { vo.getDeviceType() };
		return this.update(sql, o);
	}

	/**
	 * 查询实体设备表，确认设备类型是否被使用
	 * 
	 */
	public SqlRowSet queryDataFromEntityDevice(long deviceType) {
		String sql = " SELECT * FROM t_entity_device WHERE device_type = " + deviceType
				+ " and ED_STATUS != 1 order by ed_id desc ";
		return this.query(sql);
	}

	/**
	 * 
	 * 将资产的类型由已知类型改为未知
	 * 
	 */
	public int updAssetType(EquipmentTypeVO voi) {
		String sql = "UPDATE T_ENTITY_DEVICE t SET t.device_type = ? WHERE t.device_type = ? ";
		return this.update(sql, DeviceTypeConstant.DEVICE_TYPE_UNKNOWN, voi.getDeviceType());
	}

	/**
	 * 
	 * {删除资产类型关联的操作系统}
	 * 
	 */
	public int delAssetTypeVsOs(String deviceType) {
		String sql = "delete from t_devtype_vs_os where device_type = ?";
		return this.update(sql, deviceType);
	}

	/**
	 * 
	 * {添加资产类型关联的操作系统}
	 * 
	 */
	public int addAssetTypeVsOs(String deviceType, String osType) {
		String sql = "insert into t_devtype_vs_os (device_type,os_code) values (?,?)";
		return this.update(sql, new Object[] { deviceType, osType });
	}
}
